﻿using Microsoft.SharePoint;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace RadGridDemo
{
    class CustomerHelper
    {
        private static string connectionString = "Integrated Security=true;Initial Catalog=AdventureWorksLT2012;Server=(local)";

        public static DataTable getCustomers()
        {
            DataTable dt = new DataTable();
            SPSecurity.RunWithElevatedPrivileges(delegate()
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    if (connection.State != ConnectionState.Open)
                    {
                        connection.Open();
                    }

                    SqlCommand cmd = connection.CreateCommand();

                    cmd.CommandText = "CustomersGet";
                    cmd.CommandType = CommandType.StoredProcedure;

                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);

                    adapter.Fill(dt);

                }

            });

            return dt;
        }

        public static void addCustomer(string title, string firstName, string lastName, string company, string salesPerson, string email, string phone, string address, string city, string state, string country, string zip)
        {
            SPSecurity.RunWithElevatedPrivileges(delegate()
            {
                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                    if (conn.State != ConnectionState.Open)
                    {
                        conn.Open();
                    }


                    SqlCommand cmd = conn.CreateCommand();

                    cmd.CommandText = "CustomerAdd";
                    cmd.Parameters.Add(new SqlParameter("@Title", title));
                    cmd.Parameters.Add(new SqlParameter("@FirstName", firstName));
                    cmd.Parameters.Add(new SqlParameter("@LastName", lastName));
                    cmd.Parameters.Add(new SqlParameter("@CompanyName", company));
                    cmd.Parameters.Add(new SqlParameter("@SalesPerson", salesPerson));
                    cmd.Parameters.Add(new SqlParameter("@EmailAddress", email));
                    cmd.Parameters.Add(new SqlParameter("@Phone", phone));
                    cmd.Parameters.Add(new SqlParameter("@AddressLine1", address));
                    cmd.Parameters.Add(new SqlParameter("@City", city));
                    cmd.Parameters.Add(new SqlParameter("@StateProvince", state));
                    cmd.Parameters.Add(new SqlParameter("@CountryRegion", country));
                    cmd.Parameters.Add(new SqlParameter("@PostalCode", zip));
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.ExecuteNonQuery();
                }
            });
        }

        public static void updateCustomer(int customerID, string title, string firstName, string lastName, string company, string salesPerson, string email, string phone, string address, string city, string state, string country, string zip)
        {
            SPSecurity.RunWithElevatedPrivileges(delegate()
            {
                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                    if (conn.State != ConnectionState.Open)
                    {
                        conn.Open();
                    }

                    SqlCommand cmd = conn.CreateCommand();

                    cmd.CommandText = "CustomerUpdate";
                    cmd.Parameters.Add(new SqlParameter("@customerID", customerID));
                    cmd.Parameters.Add(new SqlParameter("@Title", title));
                    cmd.Parameters.Add(new SqlParameter("@FirstName", firstName));
                    cmd.Parameters.Add(new SqlParameter("@LastName", lastName));
                    cmd.Parameters.Add(new SqlParameter("@CompanyName", company));
                    cmd.Parameters.Add(new SqlParameter("@SalesPerson", salesPerson));
                    cmd.Parameters.Add(new SqlParameter("@EmailAddress", email));
                    cmd.Parameters.Add(new SqlParameter("@Phone", phone));
                    cmd.Parameters.Add(new SqlParameter("@AddressLine1", address));
                    cmd.Parameters.Add(new SqlParameter("@City", city));
                    cmd.Parameters.Add(new SqlParameter("@StateProvince", state));
                    cmd.Parameters.Add(new SqlParameter("@CountryRegion", country));
                    cmd.Parameters.Add(new SqlParameter("@PostalCode", zip));
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.ExecuteNonQuery();
                }
            });
        }

        public static void deleteCustomer(int customerID)
        {
            SPSecurity.RunWithElevatedPrivileges(delegate()
            {
                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                    if (conn.State != ConnectionState.Open)
                    {
                        conn.Open();
                    }

                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandText = "CustomerDel";
                    cmd.Parameters.Add(new SqlParameter("@customerID", customerID));
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.ExecuteNonQuery();
                }
            });
        }
    }
}
